package priv.mill.db;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.xml.bind.JAXBException;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.log4j.Logger;

public class DBUtils {

	private static Config config = null;
	private static Logger logger = Logger.getLogger(DBUtils.class);

	static {
		try {
			config = XMLUtils.formClassPathXML(Config.class, "db.xml");
			Class.forName(config.driver);
		} catch (UnsupportedEncodingException | JAXBException | ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) throws Exception {
		List<Map<String, Object>> query = query("show tables");
		System.out.println(query);

	}

	public static List<Map<String, Object>> query(String sql, Object... args) throws SQLException, Exception {
		Connection connection = getConn();
		PreparedStatement pre = connection.prepareStatement(sql);
		for (int i = 0; i < args.length; i++) {
			pre.setObject(i + 1, args[i]);
		}
		logger.info("sql编译完毕");
		String preString = pre.toString();
		logger.info("sql" + preString.substring(preString.indexOf(":")));
		ResultSet rs = pre.executeQuery();
		ResultSetMetaData metaData = rs.getMetaData();
		List<Map<String, Object>> list = new ArrayList<>();
		while (rs.next()) {
			Map<String, Object> map = new HashMap<>();
			for (int i = 1; i <= metaData.getColumnCount(); i++) {
				map.put(NameUtils.under2Camel(metaData.getColumnName(i)), rs.getObject(i));
			}
			list.add(map);
		}
		logger.info("获取结果集" + list.size() + "条");
		closeConn(rs, pre, connection);
		return list;
	}

	public static int update(String sql, Object... args) throws SQLException, Exception {
		Connection connection = getConn();
		PreparedStatement pre = connection.prepareStatement(sql);
		for (int i = 0; i < args.length; i++) {
			pre.setObject(i + 1, args[i]);
		}
		logger.info("sql编译完毕");
		String preString = pre.toString();
		logger.info("sql" + preString.substring(preString.indexOf(":")));
		int rs = pre.executeUpdate();

		logger.info("受影响行数" + rs + "条");
		closeConn(null, pre, connection);
		return rs;
	}

	private static void closeConn(AutoCloseable... closeAble) throws Exception {
		for (AutoCloseable autoCloseable : closeAble) {
			if (autoCloseable != null)
				autoCloseable.close();
		}
	}

	private static Connection getConn() throws SQLException {
		return DriverManager.getConnection(config.url, config.user, config.password);
	}

	public static <T> List<T> query(Class<T> class1, String sql, Object... args) throws SQLException, Exception {
		List<Map<String, Object>> query = query(sql, args);
		List<T> result = new ArrayList<>();
		for (Map<String, Object> map : query) {
			T newInstance = class1.getConstructor().newInstance();
			BeanUtils.populate(newInstance, map);
			result.add(newInstance);
		}
		return result;
	}

	public static <T> T queryOne(Class<T> class1, String sql, Object... args) throws SQLException, Exception {
		List<Map<String, Object>> query = query(sql, args);
		T newInstance = class1.getConstructor().newInstance();
		BeanUtils.populate(newInstance, query.get(0));
		return newInstance;
	}
}
